﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

/// <summary>
///Zhtjfx 的摘要说明
/// </summary>
public class Zhtjfx : IPaginationList 
{
    string selectString;
    public string tqdb_countStr;
    string ConnectStr;
    HttpRequest Request;
    public Zhtjfx(HttpRequest Request)
	{
        this.Request = Request;
        ConnectStr=  DAL.DBConnector.GetConnectString(BLL.FyDataService.GetDefaultFYDM());
        selectString = CreatSelectString();
	}

    public   string CreatSelectString()
    {

        string SFTQDB = HttpUtility.UrlDecode(Request.QueryString["SFTQDB"]);
        string JBFY = HttpUtility.UrlDecode(Request.QueryString["JBFY"]);

        string KPLB = HttpUtility.UrlDecode(Request.QueryString["KPLB"]);
        string AJLX = HttpUtility.UrlDecode(Request.QueryString["AJLX"]);

        string AH = HttpUtility.UrlDecode(Request.QueryString["AH"]);
        string AY = HttpUtility.UrlDecode(Request.QueryString["AY"]);

        string CBT = HttpUtility.UrlDecode(Request.QueryString["CBT"]);
        string ZSFG = HttpUtility.UrlDecode(Request.QueryString["ZSFG"]);

        string SXQK = HttpUtility.UrlDecode(Request.QueryString["SXQK"]);

        string JAQK = HttpUtility.UrlDecode(Request.QueryString["JAQK"]);
        string SJY = HttpUtility.UrlDecode(Request.QueryString["SJY"]);


        string TJSD_Begin = HttpUtility.UrlDecode(Request.QueryString["TJSD_Begin"]);
        string TJSD_End = HttpUtility.UrlDecode(Request.QueryString["TJSD_End"]);




        string connStr = DAL.DBConnector.GetConnectString(BLL.FyDataService.GetDefaultFYDM());
        string whereStr = "";

        if (AH != null && AH.Trim() != "") whereStr += string.Format("and xml_ah like '%{0}%'  ", AH);
        if (AY != null && AY.Trim() != "") whereStr += string.Format("and xml_sfay like '%{0}%'  ", AY);
        if (ZSFG != null && ZSFG.Trim() != "") whereStr += string.Format("and xml_uicbr like '%{0}%'  ", ZSFG);


        if (JBFY != null && JBFY.Trim() != "")
        {
            string[] dsrs = JBFY.Split(',');
            whereStr += "and ( ";
            int lastIndex = dsrs.Length - 1;
            for (int i = 0; i < dsrs.Length; i++)
            {
                whereStr += string.Format(" xml_fymc like '%{0}%' ", dsrs[i].Trim());
                if (i != lastIndex) whereStr += " or ";
            }
            whereStr += " ) ";

        }
        if (KPLB != null && KPLB.Trim() != "")
        {
            KPLB = KPLB.Replace("13", "13,18,19,20,21,22,23");
            string[] KPLBs = KPLB.Split(',');
            whereStr += "and ( ";
            int lastIndex = KPLBs.Length - 1;
            for (int i = 0; i < KPLBs.Length; i++)
            {
                whereStr += string.Format(" xml_kplb={0} ", KPLBs[i].Trim());
                if (i != lastIndex) whereStr += " or ";
            }
            whereStr += " ) ";

        }
        if (AJLX != null && AJLX.Trim() != "")
        {

            string[] AJLXs = AJLX.Split(',');
            whereStr += "and ( ";
            int lastIndex = AJLXs.Length - 1;
            for (int i = 0; i < AJLXs.Length; i++)
            {
                whereStr += string.Format(" XML_UIRXAJLX like '%{0}%' ", AJLXs[i].Trim());
                if (i != lastIndex) whereStr += " or ";
            }
            whereStr += " ) ";

        }
        if (CBT != null && CBT.Trim() != "")
        {

            string[] CBTs = CBT.Split(',');
            whereStr += "and ( ";
            int lastIndex = CBTs.Length - 1;
            for (int i = 0; i < CBTs.Length; i++)
            {
                whereStr += string.Format(" XML_CBSPTMC like '%{0}%' ", CBTs[i].Trim());
                if (i != lastIndex) whereStr += " or ";
            }
            whereStr += " ) ";

        }
        if (SJY != null && SJY.Trim() != "")
        {

            string[] SJYs = SJY.Split(',');
            whereStr += "and ( ";
            int lastIndex = SJYs.Length - 1;
            for (int i = 0; i < SJYs.Length; i++)
            {
                whereStr += string.Format(" xml_uisjy like '%{0}%' ", SJYs[i].Trim());
                if (i != lastIndex) whereStr += " or ";
            }
            whereStr += " ) ";

        }

        #region 同期对比条件语句
        string tqdb_whereStr = "";//同期对比条件语句
         tqdb_countStr = "";
       
        if (SFTQDB != null && SFTQDB.Trim() == "1")
        {
            tqdb_whereStr = whereStr;
            string tqdb_TJSD_Begin = "";
            string tqdb_TJSD_End = "";
            if (TJSD_Begin != null && TJSD_Begin.Trim() != "") tqdb_TJSD_Begin = DateTime.Parse(TJSD_Begin).AddYears(-1).ToString("yyyy-MM-dd");
            if (TJSD_End != null && TJSD_End.Trim() != "") tqdb_TJSD_End = DateTime.Parse(TJSD_End).AddYears(-1).ToString("yyyy-MM-dd");
            if (JAQK != null && JAQK.Trim() != "")
            {


                switch (JAQK.Trim())
                {
                    case "未结":
                        tqdb_whereStr += " and (XML_JAFS is  null or XML_JAFS ='') ";
                        if (tqdb_TJSD_Begin != null && tqdb_TJSD_Begin.Trim() != "") tqdb_whereStr += string.Format(" and xml_larq >= '{0}'  ", tqdb_TJSD_Begin);
                        if (tqdb_TJSD_End != null && tqdb_TJSD_End.Trim() != "") tqdb_whereStr += string.Format(" and xml_larq  <='{0}'  ", tqdb_TJSD_End);
                        break;
                    case "已结":
                        tqdb_whereStr += " and (XML_JAFS is not null or XML_JAFS<>'') ";
                        if (tqdb_TJSD_Begin != null && tqdb_TJSD_Begin.Trim() != "") tqdb_whereStr += string.Format(" and xml_jarq >= '{0}'  ", tqdb_TJSD_Begin);
                        if (tqdb_TJSD_End != null && tqdb_TJSD_End.Trim() != "") tqdb_whereStr += string.Format(" and xml_jarq  <='{0}'  ", tqdb_TJSD_End);
                        break;
                    case "所有":
                        string larqstr = "";
                        string larqstr_l = "";
                        string larqstr_r = "";
                        string jarqstr = "";
                        string jarqstr_l = "";
                        string jarqstr_r = "";

                        if (tqdb_TJSD_Begin != null && tqdb_TJSD_Begin.Trim() != "") larqstr_l = string.Format(" xml_larq >= '{0}'  ", tqdb_TJSD_Begin);
                        if (tqdb_TJSD_End != null && tqdb_TJSD_End.Trim() != "") larqstr_r = string.Format(" xml_larq  <='{0}'  ", tqdb_TJSD_End);

                        if (larqstr_l != "" && larqstr_r != "")
                        {
                            larqstr = "(" + larqstr_l + " and " + larqstr_r + ")";
                        }
                        else if (larqstr_l != "")
                        {
                            larqstr = "(" + larqstr_l + ")";
                        }
                        else if (larqstr_r != "")
                        {
                            larqstr = "(" + larqstr_r + ")";
                        }

                        if (tqdb_TJSD_Begin != null && tqdb_TJSD_Begin.Trim() != "") jarqstr_l = string.Format(" xml_jarq >= '{0}'  ", tqdb_TJSD_Begin);
                        if (tqdb_TJSD_End != null && tqdb_TJSD_End.Trim() != "") jarqstr_r = string.Format(" xml_jarq  <='{0}'  ", tqdb_TJSD_End);

                        if (jarqstr_l != "" && jarqstr_r != "")
                        {
                            jarqstr = "(" + larqstr_l + " and " + jarqstr_r + ")";
                        }
                        else if (jarqstr_l != "")
                        {
                            jarqstr = "(" + jarqstr_l + ")";
                        }
                        else if (jarqstr_r != "")
                        {
                            jarqstr = "(" + jarqstr_r + ")";
                        }

                        if (larqstr != "" && jarqstr != "")
                        {
                            tqdb_whereStr += " and (" + larqstr + " or " + jarqstr + ")";
                        }
                        else if (larqstr != "")
                        {
                            tqdb_whereStr += " and " + larqstr;
                        }
                        else if (jarqstr != "")
                        {
                            tqdb_whereStr += " and " + jarqstr;
                        }

                        break;

                }


            }
            tqdb_countStr = string.Format("select count(*) from B_ZXB_AJXX where 1=1 {0}", tqdb_whereStr);

           
        }
        #endregion

        if (JAQK != null && JAQK.Trim() != "")
        {


            switch (JAQK.Trim())
            {
                case "未结":
                    whereStr += " and (XML_JAFS is  null or XML_JAFS ='') ";
                    if (TJSD_Begin != null && TJSD_Begin.Trim() != "") whereStr += string.Format(" and xml_larq >= '{0}'  ", TJSD_Begin);
                    if (TJSD_End != null && TJSD_End.Trim() != "") whereStr += string.Format(" and xml_larq  <='{0}'  ", TJSD_End);
                    break;
                case "已结":
                    whereStr += " and (XML_JAFS is not null or XML_JAFS<>'') ";
                    if (TJSD_Begin != null && TJSD_Begin.Trim() != "") whereStr += string.Format(" and xml_jarq >= '{0}'  ", TJSD_Begin);
                    if (TJSD_End != null && TJSD_End.Trim() != "") whereStr += string.Format(" and xml_jarq  <='{0}'  ", TJSD_End);
                    break;
                case "所有":

                    //string s1 = "";
                    //string s2="";
                    //if (TJSD_Begin != null && TJSD_Begin.Trim() != "") s1 += string.Format(" and xml_larq >= '{0}'  ", TJSD_Begin);
                    //if (TJSD_End != null && TJSD_End.Trim() != "") s1 += string.Format(" and xml_larq  <='{0}'  ", TJSD_End);

                    //if (TJSD_Begin != null && TJSD_Begin.Trim() != "") s2 += string.Format(" and xml_jarq >= '{0}'  ", TJSD_Begin);
                    //if (TJSD_End != null && TJSD_End.Trim() != "") s2 += string.Format(" and xml_jarq  <='{0}'  ", TJSD_End);
                    string larqstr = "";
                    string larqstr_l = "";
                    string larqstr_r = "";
                    string jarqstr = "";
                    string jarqstr_l = "";
                    string jarqstr_r = "";

                    if (TJSD_Begin != null && TJSD_Begin.Trim() != "") larqstr_l = string.Format(" xml_larq >= '{0}'  ", TJSD_Begin);
                    if (TJSD_End != null && TJSD_End.Trim() != "") larqstr_r = string.Format(" xml_larq  <='{0}'  ", TJSD_End);

                    if (larqstr_l != "" && larqstr_r != "")
                    {
                        larqstr = "(" + larqstr_l + " and " + larqstr_r + ")";
                    }
                    else if (larqstr_l != "")
                    {
                        larqstr = "(" + larqstr_l + ")";
                    }
                    else if (larqstr_r != "")
                    {
                        larqstr = "(" + larqstr_r + ")";
                    }

                    if (TJSD_Begin != null && TJSD_Begin.Trim() != "") jarqstr_l = string.Format(" xml_jarq >= '{0}'  ", TJSD_Begin);
                    if (TJSD_End != null && TJSD_End.Trim() != "") jarqstr_r = string.Format(" xml_jarq  <='{0}'  ", TJSD_End);

                    if (jarqstr_l != "" && jarqstr_r != "")
                    {
                        jarqstr = "(" + larqstr_l + " and " + jarqstr_r + ")";
                    }
                    else if (jarqstr_l != "")
                    {
                        jarqstr = "(" + jarqstr_l + ")";
                    }
                    else if (jarqstr_r != "")
                    {
                        jarqstr = "(" + jarqstr_r + ")";
                    }

                    if (larqstr != "" && jarqstr != "")
                    {
                        whereStr += " and (" + larqstr + " or " + jarqstr + ")";
                    }
                    else if (larqstr != "")
                    {
                        whereStr += " and " + larqstr;
                    }
                    else if (jarqstr != "")
                    {
                        whereStr += " and " + jarqstr;
                    }



                    break;

            }


        }






        string sortName = Request["sort"];
        string sortOrder = Request["order"];
        if (string.IsNullOrEmpty(sortName)) sortName = "xml_ah";
        if (string.IsNullOrEmpty(sortOrder)) sortOrder = "DESC";
        string sortStr = string.Format(" {0} {1} ",sortName,sortOrder );
        if (sortName.Trim().ToLower() == "xml_ah") sortStr = string.Format(" xml_ahyear {0},xml_ahzt {0},xml_ahz {0},int(xml_ahh) {0}", sortOrder);


        string selectStr = string.Format("select #NOTEID,XML_FYDM,XML_FYMC,XML_AH,XML_SFAY,XML_CBSPTMC,XML_UICBR,xml_ahyear,char(xml_larq) as xml_larq,char(XML_JARQ) as XML_JARQ,XML_JAFS,XML_AHZT,xml_kplb,XML_UIRXAJLX,XML_GROAJZT,xml_uisjy from B_ZXB_AJXX where 1=1 {0} order by {1} ", whereStr, sortStr);
        // selectStr = "select * from B_ZXB_AJXX  fetch first 2 rows only";
        return selectStr;
    }
    public int GetTQDB_TotalCount()
    {

     
        int i = 0;
        try
        {
            i = (int)DAL.DBConnector.ExcecuteScalar(tqdb_countStr, ConnectStr);
        }
        catch (Exception ex) { }
        return i;
    }

    #region IPaginationList 成员

    DataTable IPaginationList.GetPaginationData()
    {
        string ss = PaginationTools.FormatPaginationSelectString(selectString, "RowNumx", Request["rows"], Request["page"]);
        return DAL.DBConnector.GetSqlSelectData(ss, ConnectStr);
    }

    int IPaginationList.GetTotalCount()
    {
        string ss = PaginationTools.FormatPaginationCountString(selectString);
        int i = 0;
        try
        {
            i = (int)DAL.DBConnector.ExcecuteScalar(ss, ConnectStr);
        }
        catch (Exception ex) { }
        return i;
    }

    DataTable IPaginationList.GetTotalData()
    {
       return  DAL.DBConnector.GetSqlSelectData(selectString, ConnectStr);
    }

    #endregion
}
